--EXEC SP_GET_ALL_NONVALUE_INVENTORY_LOTS 'Die Bank', 0, 0, 0

CREATE PROC SP_GET_ALL_NONVALUE_INVENTORY_LOTS
( 
 @PROCESS_TYPE   VARCHAR(50),
 @PROCESSSTEP_ID INT,
 @LOCATION_ID INT,
 @SEARCH_LOT_ID INT

)
AS
BEGIN
	SET NOCOUNT ON
	CREATE TABLE #RESULTS ( LOT_ID INT,PROCESSSTEP_ID INT, LOCATION_ID INT, PART_NUMBER VARCHAR(50), Lot_Number VARCHAR(50), Fab_Lot_Number VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT, DATE_IN DATETIME, WAFER_IDS VARCHAR(100), VENDOR_NAME VARCHAR(50), DATE_CODE VARCHAR(50), LOT_COMMENTS VARCHAR(500) ) 
	
	DECLARE @QUERYSTRING	VARCHAR(4000)

	SET @QUERYSTRING = N'
 	INSERT INTO #RESULTS 
 	SELECT	LT.LOT_ID, LT.PROCESSSTEP_ID, LT.LOCATION_ID, PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, SL_SCRAPPED_WAFER_QTY, SL_SCRAPPED_DIE_QTY, SL_SCRAPPED_DATE, SL_SCRAPPED_WAFER_IDS, L_VENDOR_NAME, LOT_DATE_CODE, SL_COMMENTS
	FROM LOT_TABLE LT, SCRAPPED_LOTS SL, PROCESS_STEP PS, LOCATION L, PRODUCT P 
	WHERE LT.LOT_ID = SL.LOT_ID AND
	LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND
	LT.LOCATION_ID = L.LOCATION_ID AND ( SL_SCRAPPED_WAFER_QTY > 0 OR SL_SCRAPPED_DIE_QTY > 0 ) AND IS_NONVALUE_INVENTORY = ''0'' '
	
		IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING + ' AND PS_TYPE = '''+ @PROCESS_TYPE+''''
	 	END
	 	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0  )
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND PS.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
	 	END
		IF ( @LOCATION_ID IS NOT NULL AND @LOCATION_ID > 0  )
		BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOCATION_ID = ' + CONVERT(VARCHAR,@LOCATION_ID)
	 	END
	 	IF ( @SEARCH_LOT_ID IS NOT NULL AND @SEARCH_LOT_ID > 0  )
		BEGIN
 			SET @QUERYSTRING = @QUERYSTRING +' AND LT.LOT_ID = ' + CONVERT(VARCHAR,@SEARCH_LOT_ID)
 		END

		SET @QUERYSTRING = @QUERYSTRING + ' ORDER BY P.PART_NUMBER'
		--PRINT '@QUERYSTRING :- '+@QUERYSTRING
	 EXEC (@QUERYSTRING)

		
	SELECT 
	 LOT_ID ,PROCESSSTEP_ID , LOCATION_ID , PART_NUMBER , Lot_Number , Fab_Lot_Number, WAFER_QTY_IN, QTY_IN , CONVERT(VARCHAR,DATE_IN, 101) AS DATE_IN , WAFER_IDS, VENDOR_NAME, DATE_CODE,LOT_COMMENTS 
	FROM #RESULTS ORDER BY LOT_NUMBER
	
END